﻿/****************************** Module Header ******************************\
 * Module Name:  EntityDataHelper.cs
 * Module Version:  3.0.0 (2012-4-25)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to collections DbParameter. It supplies the useful method
 * of add DbParameter.
 * 
 * log:2012-11-29 by sutroon
 * 重构,由DALDBHelper改为EntityDBHelper
 * log:2013-2-16 by sutroon
 * 重构,由EntityDBHelper改为EntityDataHelper,并兼容BusinessHelper功能,并增加错误消息收集器
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text;

namespace UCL.Data.ORM
{
    /// <summary>
    /// 数据实体对象数据库帮助类
    /// </summary>
    public class EntityDataHelper
    {
        #region 私有字段
        /// <summary>
        /// dbh实例对象
        /// </summary>
        private IDatabaseHelper dbh;

        /// <summary>
        /// DbCommand实例对象
        /// </summary>
        private DbCommand cmd;

        /// <summary>
        /// 配置文件数据库连接信息
        /// <para>[0]=ConnectionName,[1]=ProviderName</para>
        /// </summary>
        string connectionString, providerName;
        #endregion

        /// <summary>
        /// 消息收集器
        /// </summary>
        public IList<string> MessageSet { get; set; }

        /// <summary>
        /// 获取最后执行过的Sql语句
        /// </summary>
        public string LastSql { get; set; }

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="connectionInfo"></param>
        public EntityDataHelper(string connectionString, string providerName)
        {
            this.connectionString = connectionString;
            this.providerName = providerName;
            this.dbh = new SQLServerHelper(connectionString);
            this.cmd = dbh.GetDbCommand();
            this.MessageSet = new List<string>();
        }

        #region 私有方法

        /// <summary>
        /// 检测dbh是否有消息，如果有则合并到当前的消息收集器
        /// </summary>
        private void CheckDbhMessage()
        {
            if (dbh.MessageSet.Count > 0) MessageSet = MessageSet.Concat(dbh.MessageSet).ToList();
        }
        #endregion

        #region 增删改查

        /// <summary>
        /// 添加实体对象对应的数据库记录,返回所影响的行数
        /// <para>如：AddEntity&lt;tUser&gt;(user);</para>
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        public int AddEntity<T>(T e)
        {
            IList<DbParameter> lstParas = new List<DbParameter>();
            StringBuilder sbFields = new StringBuilder(), sbValues = new StringBuilder();
            Type type = typeof(T);
            object oVal;
            string sDefv, sName;
            DBColumnAttribute attrCol;
            DbParameter para;
            foreach (FieldInfo pi in type.GetFields(BindingFlags.Instance | BindingFlags.NonPublic))
            {
                attrCol = pi.GetCustomAttributes(typeof(DBColumnAttribute), false).FirstOrDefault() as DBColumnAttribute;
                if (attrCol == null) break;
                oVal = pi.GetValue(e);
                if (attrCol.IsKey && pi.FieldType.IsGType<int>()) continue; // 跳过自增长类型的主键字段
                sDefv = string.IsNullOrEmpty(attrCol.DefaultValue) ? string.Empty : attrCol.DefaultValue;
                if (oVal == null && sDefv.Length > 0)
                {
                    if (pi.FieldType.IsGType<DateTime>()) oVal = DateTime.Now;
                    if (pi.FieldType.IsGType<bool>()) oVal = sDefv.ToBoolean();
                    if (pi.FieldType.IsGType<Guid>()) oVal = Guid.NewGuid();
                    if (oVal == null) oVal = sDefv;
                }
                sName = pi.Name.TrimStart('_');
                if (oVal == null)
                {
                    if (!attrCol.CanBeNull) MessageSet.Add(string.Format("{0}不能为空!", string.IsNullOrEmpty(attrCol.Alias) ? sName : attrCol.Alias));
                    continue;
                }
                sbFields.AppendFormat(" [{0}],", sName);
                sbValues.AppendFormat(" @{0},", sName);
                para = this.cmd.CreateParameter();
                para.ParameterName = "@{0}".Fmt(sName);
                para.DbType = dbh.ClrTypeToDbType(pi.FieldType);
                para.Value = oVal;
                lstParas.Add(para);
            }
            LastSql = string.Format("insert into [{0}] ({1}) values ({2})", type.Name, sbFields.ToString().TrimEnd(','), sbValues.ToString().TrimEnd(','));
            int iOut = 0;
            if (MessageSet.Count == 0)
            {
                iOut = this.dbh.ExecuteNonQuery(LastSql, lstParas.ToArray());
                this.CheckDbhMessage();
            }
            return iOut;
        }

        /// <summary>
        /// 添加实体对象对应的数据库记录,返回 该记录的主键值
        /// <para>如：InsertEntity&lt;tUser&gt;(user);</para>
        /// </summary>
        /// <param name="e"></param>
        /// <param name="orderBy">获取最后一条语句的命令</param>
        /// <returns></returns>
        public object InsertEntity<T>(T e, string orderBy = "[CreatedTime] desc") where T : class,new()
        {
            this.AddEntity(e);
            FieldInfo fi = EntityHelper.GetKeyField<T>();
            if (fi == null) return null;
            T t = this.GetLastEntity<T>(orderBy);
            return fi.GetValue(e);
        }

        /// <summary>
        /// 更新实体对象对应的数据库记录
        /// <para>如：UpdateEntity&lt;tUser&gt;(user);</para>
        /// </summary>
        /// <param name="e"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int UpdateEntity<T>(T e, string condition = "")
        {
            IList<DbParameter> lstParas = new List<DbParameter>();
            StringBuilder sb = new StringBuilder();
            Type type = typeof(T);
            object oVal;
            DbParameter para;
            DBColumnAttribute attrCol;
            string sName;
            foreach (FieldInfo pi in type.GetFields(BindingFlags.Instance | BindingFlags.NonPublic))
            {
                attrCol = pi.GetCustomAttributes(typeof(DBColumnAttribute), false).FirstOrDefault() as DBColumnAttribute;
                if (attrCol == null) break;
                oVal = pi.GetValue(e);
                sName = pi.Name.TrimStart('_');
                if (attrCol.IsKey)
                {
                    condition = this.SetPrimaryKeyCondition(pi.FieldType, sName, oVal);
                    continue;
                }
                if (oVal == null) continue;
                sb.AppendFormat(" [{0}]=@{0},", sName);
                para = this.cmd.CreateParameter();
                para.ParameterName = "@{0}".Fmt(sName);
                para.DbType = dbh.ClrTypeToDbType(pi.FieldType);
                para.Value = oVal;
                lstParas.Add(para);
            }
            if (condition.Length == 0)
            {
                MessageSet.Add("Where条件未设置");
                return 0;
            }
            if (sb.Length == 0)
            {
                MessageSet.Add("无有效更新字段");
                return 0;
            }
            LastSql = "update [{0}] set {1} where {2}".Fmt(type.Name, sb.ToString().TrimEnd(','), condition);
            int iOut = this.dbh.ExecuteNonQuery(LastSql, lstParas.ToArray());
            this.CheckDbhMessage();
            return iOut;
        }

        /// <summary>
        /// 批量更新数据库记录
        /// <para>如：UpdateEntity&lt;tUser&gt;("State=1,ModifiedTime=getdate()","State=0");</para>
        /// </summary>
        /// <param name="fields"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int UpdateEntity<T>(string fields, string condition)
        {
            LastSql = "update [{0}] set {1} {2}".Fmt(typeof(T).Name, fields, SetSqlCondition<T>(condition));
            int iOut = this.dbh.ExecuteNonQuery(LastSql);
            this.CheckDbhMessage();
            return iOut;
        }

        /// <summary>
        /// 删除数据库记录,返回所影响的行数
        /// <para>条件可为主键值或自定义条件(不含where关键词)</para>
        /// <para>如：DeleteEntity&lt;tUser&gt;("sutroon") 或 DeleteEntity&lt;tUser&gt;("State=400");</para>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition">主键值或自定义条件(不含where关键词)</param>
        /// <returns></returns>
        public int DeleteEntity<T>(string condition)
        {
            LastSql = string.Format("delete from [{0}] {1}", typeof(T).Name, SetSqlCondition<T>(condition));
            int iOut = this.dbh.ExecuteNonQuery(LastSql);
            this.CheckDbhMessage();
            return iOut;
        }

        /// <summary>
        /// 获取指定查询条件的实体对象
        /// <para>查询条件可以是主键的值或自定义查询条件</para>
        /// <para>如：GetEntity&lt;tUser&gt;("sutroon"); 或 GetEntity&lt;tUser&gt;("Name like 'su%' and sex=1");</para>
        /// </summary>
        /// <param name="condition">主键的值或自定义查询条件</param>
        /// <param name="paras">可选的参数化查询参数</param>
        /// <returns></returns>
        public T GetEntity<T>(string condition, params DbParameter[] paras) where T : class,new()
        {
            T t = null;
            LastSql = this.GetSelectSqlStatement<T>(SetSqlCondition<T>(condition), 1);
            using (IDataReader dr = this.dbh.ExecuteDataTable(LastSql, paras).CreateDataReader())
            {
                while (dr.Read())
                {
                    t = new T();
                    EntityHelper.EntityFromDataReader<T>(ref t, dr);
                }
            }
            this.CheckDbhMessage();
            return t;
        }

        /// <summary>
        /// 获取实体对象列表
        /// <para>如：GetEntityList&lt;tUser&gt;("Sex=0 order by CreatedTime desc",10);</para>
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="count">记录数量，0=不限</param>
        /// <returns></returns>
        public IList<T> GetEntityList<T>(string condition = "", int count = 0) where T : new()
        {
            IList<T> list = new List<T>();
            T t;
            LastSql = this.GetSelectSqlStatement<T>(condition, count);
            using (IDataReader dr = this.dbh.ExecuteDataTable(LastSql).CreateDataReader())
            {
                while (dr.Read())
                {
                    t = new T();
                    EntityHelper.EntityFromDataReader<T>(ref t, dr);
                    list.Add(t);
                }
            }
            this.CheckDbhMessage();
            return list;
        }

        /// <summary>
        /// 查询符合条件的记录的数量
        /// <para>如：GetEntityCount&lt;tUser&gt;("Sex=0");</para>
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public int GetEntityCount<T>(string condition, params DbParameter[] paras)
        {
            LastSql = "select count(0) from [{0}] {1}".Fmt(typeof(T).Name, SetSqlCondition<T>(condition));
            DataTable dt = this.dbh.ExecuteDataTable(LastSql, paras);
            this.CheckDbhMessage();
            return dt.Rows.Count == 0 ? 0 : (int)dt.Rows[0][0];
        }

        /// <summary>
        /// 检测指定条件的记录是否存在
        /// <para>如：ExistsEntity&lt;tUser&gt;("Name='sutroon'");</para>
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public bool ExistsEntity<T>(string condition)
        {
            return this.GetEntityCount<T>(condition) > 0;
        }

        /// <summary>
        /// 获取最后一条记录的实体对象
        /// <para>如：GetLastEntity&lt;tUser&gt;("CreatedTime desc");</para>
        /// </summary>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public T GetLastEntity<T>(string orderBy = "[CreatedTime] desc") where T : class,new()
        {
            if (orderBy.ToLower().IndexOf("order by") == -1) orderBy = string.Format("order by {0}", orderBy);
            orderBy = this.GetSelectSqlStatement<T>(orderBy, 1);
            FieldInfo pi = EntityHelper.GetKeyField<T>();
            if (pi == null) return default(T);
            T t = null;
            using (IDataReader dr = this.dbh.ExecuteDataTable(orderBy).CreateDataReader())
            {
                while (dr.Read())
                {
                    t = new T();
                    EntityHelper.EntityFromDataReader<T>(ref t, dr);
                }
            }
            return t;
        }

        /// <summary>
        /// 获取符合条件的指定列名称的值的列表
        /// <para>如果没有指定列的名称则默认是主键列</para>
        /// <para>如：GetFieldValueList&lt;tUser,string&gt;("Sex=0","Name");</para>
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public IList<V> GetFieldValueList<T, V>(string condition, string field = "")
        {
            LastSql = "select {0} from {1} {2}".Fmt(string.IsNullOrEmpty(field) ? EntityHelper.GetKeyField<T>().Name : field, typeof(T).Name, SetSqlCondition<T>(condition));
            return this.dbh.GetColumnValues<V>(LastSql);
        }

        #endregion

        #region 其他数据库交互方法

        /// <summary>
        /// 创建实体对象对应的数据表
        /// </summary>
        public void CreateTableForEntity<T>()
        {
            /*Type type = typeof(T);
            DBTableAttribute attrTb = type.GetCustomAttributes(typeof(DBTableAttribute), false).FirstOrDefault() as DBTableAttribute;
            if (attrTb != null && !attrTb.Disabled) return; // 如果实体类指示Enable=false则退出数据库创建

            StringBuilder sb = new StringBuilder();
            string typeName, len, strAutoIncrement = dbh.Sql_GetAutoIncrementSql();
            DBColumnAttribute attrCol;
            foreach (FieldInfo pi in type.GetFields(BindingFlags.Instance | BindingFlags.NonPublic))
            {
                attrCol = pi.GetCustomAttributes(typeof(DBColumnAttribute), false).FirstOrDefault() as DBColumnAttribute;
                if (attrCol == null) break;
                typeName = dbh.ClrTypeToDbTypeName(pi.FieldType);
                len = string.IsNullOrEmpty(attrCol.Length) ? "255" : attrCol.Length;
                if (attrCol.IsKey)
                {
                    switch (typeName)
                    {
                        case "varchar":
                            sb.AppendFormat(" {0} {1}({2}) primary key,", pi.Name, typeName, len);
                            break;
                        case "uniqueidentifier":
                            sb.AppendFormat(" {0} {1} not null,", pi.Name, typeName);
                            break;
                        default:
                            sb.AppendFormat(" {0} {1} primary key {2},", pi.Name, typeName, strAutoIncrement);
                            break;
                    }
                    continue;
                }
                if (typeName == "varchar")
                {
                    if (attrCol.DbType.ToLower().IsIn("text", "xml")) typeName = attrCol.DbType.ToLower();
                    if (typeName == "text" || typeName == "xml")
                    {
                        sb.AppendFormat(" {0} {1},", pi.Name, typeName);
                        continue;
                    }
                    sb.AppendFormat(" {0} {1}({2}),", pi.Name, typeName, len);
                    continue;
                }
                sb.AppendFormat(" {0} {1},", pi.Name, typeName);

            }
            string sql = "create table [{0}] ({1})".Fmt(type.Name, sb.ToString().TrimEnd(','));*/
            string sql = EntityHelper.GetCreateTableTextByType(typeof(T),providerName);
            this.dbh.ExecuteNonQuery(sql);
        }
        #endregion

        #region 存储过程操作方法
        /// <summary>
        /// 获取指定存储过程的实体对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public T GetEntityByProc<T>(string procedureName, params System.Data.Common.DbParameter[] dbParameters) where T : class,new()
        {
            T t = null;
            using (IDataReader dr = dbh.ExecuteProcedureDataTable(procedureName, dbParameters).CreateDataReader())
            {
                while (dr.Read())
                {
                    t = new T();
                    EntityHelper.EntityFromDataReader<T>(ref t, dr);
                }
            }
            return t;
        }

        /// <summary>
        /// 获取指定存储过程的实体对象列表
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public IList<T> GetEntityListByProc<T>(string procedureName, params System.Data.Common.DbParameter[] dbParameters) where T : new()
        {
            IList<T> list = new List<T>();
            using (IDataReader dr = dbh.ExecuteProcedureDataTable(procedureName, dbParameters).CreateDataReader())
            {
                T t;
                while (dr.Read())
                {
                    t = new T();
                    EntityHelper.EntityFromDataReader<T>(ref t, dr);
                    list.Add(t);
                }
            }
            return list;
        }
        #endregion

        #region 与实体对象无关的方法

        /// <summary>
        /// 获取查询语句
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="count">记录数量，如果是0则表示不限</param>
        /// <returns></returns>
        public string GetSelectSqlStatement<T>(string condition, int count = 0)
        {
            if (condition.Length > 12 && condition.TrimStart().ToLower().Substring(0, 7) == "select ") return condition;
            StringBuilder sb = new StringBuilder();
            Type type = typeof(T);
            foreach (PropertyInfo pi in type.GetProperties())
            {
                sb.AppendFormat(" [{0}],", pi.Name);
            }
            if (count > 0)
            {
                return dbh.Sql_GetSelectTopNRecordSql(type.Name, sb.ToString().TrimEnd(','), dbh.Sql_SetConditionSql(condition), count);
            }
            return "select {0} from {1} {2}".Fmt(sb.ToString().TrimEnd(','), type.Name, dbh.Sql_SetConditionSql(condition));
        }

        /// <summary>
        /// 获取分页查询语句
        /// <para>2012-10-16</para>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <param name="orderby"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public string GetSelectPagingSqlStatement<T>(string condition, string orderby, int pageIndex, int pageSize)
        {
            if (pageIndex < 1) pageIndex = 1;
            if (condition.Length > 12 && condition.TrimStart().ToLower().Substring(0, 7) == "select ") return condition;
            StringBuilder sb = new StringBuilder();
            Type type = typeof(T);
            foreach (PropertyInfo pi in type.GetProperties())
            {
                sb.AppendFormat(" [{0}],", pi.Name);
            }
            if (pageIndex == 1) return "select top {3} {0} from {1} {2}".Fmt(sb.ToString().TrimEnd(','), type.Name, dbh.Sql_SetConditionSql(condition, orderby), pageSize);
            return "select {2} from(select row_number() over (order by {5}) as row_num,{2} from {3} {4}) as page_table where row_num between {0} and {1};".Fmt((pageIndex - 1) * pageSize + 1, pageIndex * pageSize, sb.ToString().TrimEnd(','), type.Name, dbh.Sql_SetConditionSql(condition, ""), orderby);
        }

        /// <summary>
        /// 设置主键查询条件
        /// </summary>
        /// <param name="propertyType"></param>
        /// <param name="k"></param>
        /// <param name="v"></param>
        /// <returns></returns>
        private string SetPrimaryKeyCondition(Type propertyType, string k, object v)
        {
            if (propertyType.IsGType<int>())
            {
                return "{0}={1}".Fmt(k, v);
            }
            return "{0}='{1}'".Fmt(k, v);
        }

        public string SetSqlCondition<T>(string condition)
        {
            if (condition.Length == 0) return string.Empty;
            if (!condition.ContainsWords("=", " like ", " > ", " < ", " <> ", " in ", " is ", " "))
            {
                FieldInfo pi = EntityHelper.GetKeyField<T>();
                if (pi == null) return string.Empty;
                condition = this.SetPrimaryKeyCondition(pi.FieldType, pi.Name.TrimStart('_'), condition);
            }
            return dbh.Sql_SetConditionSql(condition); ;
        }

        /// <summary>
        /// 获取数据表对象
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataTable GetTable<T>(string condition = "")
        {
            LastSql = this.GetSelectSqlStatement<T>(SetSqlCondition<T>(condition));
            DataTable dt = this.dbh.ExecuteDataTable(LastSql);
            dt.TableName = typeof(T).Name;
            return dt;
        }

        /// <summary>
        /// 获取IDataReader数据集对象
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public IDataReader GetReader<T>(string condition = "")
        {
            LastSql = this.GetSelectSqlStatement<T>(SetSqlCondition<T>(condition));
            return this.dbh.ExecuteDataTable(LastSql).CreateDataReader();
        }
        #endregion

        #region 分页列表

        /// <summary>
        /// 获取经过分页后的记录列表
        /// <para>2012-10-16 修改为数据库分页，效率提高了很多</para>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="orderBy"></param>
        /// <param name="pageid"></param>
        /// <param name="pagesize"></param>
        /// <param name="recordcount"></param>
        /// <param name="pagecount"></param>
        /// <returns></returns>
        public virtual IList<T> GetEntityPagingList<T>(string whereSql, string orderBySql, int pageid, int pagesize, out int recordcount, out int pagecount) where T : new()
        {
            // 统计信息
            recordcount = this.GetEntityCount<T>(whereSql);
            if (recordcount == 0)
            {
                pagecount = 0;
                return new List<T>();
            }
            pagecount = recordcount / pagesize;
            if (recordcount % pagesize > 0) pagecount += 1;
            // 列表分页
            IList<T> list = new List<T>();
            T t;
            using (IDataReader dr = this.dbh.ExecuteDataTable(this.GetSelectPagingSqlStatement<T>(whereSql, orderBySql, pageid, pagesize)).CreateDataReader())
            {
                while (dr.Read())
                {
                    t = new T();
                    EntityHelper.EntityFromDataReader<T>(ref t, dr);
                    list.Add(t);
                }
            }
            return list;
            //PaggerGeneric<T> dbp = new PaggerGeneric<T>(pageid, pagesize);
            //dbp.BindDataSource(this.GetEntityList<T>(sql));
            //recordcount = dbp.RecordCount;
            //pagecount = dbp.PageCount;
            //return dbp.ListSet;
        }

        public virtual DataTable GetEntityPagingTable<T>(string whereSql, string orderBySql, int pageid, int pagesize, out int recordcount, out int pagecount) where T : new()
        {
            recordcount = this.GetEntityCount<T>(whereSql);
            if (recordcount == 0)
            {
                pagecount = 0;
                return new DataTable();
            }
            pagecount = recordcount / pagesize;
            if (recordcount % pagesize > 0) pagecount += 1;
            LastSql = this.GetSelectPagingSqlStatement<T>(whereSql, orderBySql, pageid, pagesize);
            DataTable dt=this.dbh.ExecuteDataTable(LastSql);
            CheckDbhMessage();
            return dt;
        }

        /// <summary>
        /// 获取经过分页后的记录列表
        /// <para>2012-9-25</para>
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pageid"></param>
        /// <param name="pagesize"></param>
        /// <param name="recordcount"></param>
        /// <param name="pagecount"></param>
        /// <returns></returns>
        public virtual DataTable GetPagingDataTable(string sql, int pageid, int pagesize, out int recordcount, out int pagecount)
        {
            PagerDataTable dbp = new PagerDataTable(pageid, pagesize);
            dbp.BindDataSource(this.dbh.ExecuteDataTable(sql));
            recordcount = dbp.RecordCount;
            pagecount = dbp.PageCount;
            return dbp.ListSet;

        }

        /// <summary>
        /// 获取符合条件的表格
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <param name="fields"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public virtual DataTable GetEntityDataTable<T>(string condition = "", string fields = "*", int count = 0)
        {
            string limitcount = count == 0 ? string.Empty : string.Format(" top {0} ", count);
            LastSql = string.Format("select {3}{0} from {1} {2}", fields, typeof(T).Name, SetSqlCondition<T>(condition), limitcount);
            return this.dbh.ExecuteDataTable(LastSql);
        }


        /// <summary>
        /// 获取分类树形数据(Json格式)
        /// <para>todo:去除rootname参数</para>
        /// <para>如：GetJsonTreeData("select [id],[title] from [tmenu] where [parentid]='{0}' order by id", pid);</para>
        /// </summary>
        /// <param name="sqlTemplate"></param>
        /// <param name="parentID"></param>
        /// <param name="rootname"></param>
        /// <param name="namefield"></param>
        /// <param name="valuefield"></param>
        /// <param name="childfield"></param>
        /// <returns></returns>
        public string GetJsonTreeData(string sqlTemplate, Guid parentID, string rootname = "请选择分类", string namefield = "n", string valuefield = "v", string childfield = "c")
        {
            if (sqlTemplate.IndexOf("{0}") == -1) return string.Format("{{{0}:'',{1}:'禁止死循环查询！'}}", namefield, valuefield);
            StringBuilder sb = new StringBuilder();
            sb.Append("[");
            foreach (DataRow dr in this.dbh.ExecuteDataTable(sqlTemplate.Fmt(parentID)).Rows)
            {
                sb.AppendFormat("{{{0}:'{1}',{2}:'{3}',{4}:{5}}},", namefield, dr[1], valuefield, dr[0], childfield, this.GetJsonTreeData(sqlTemplate, (Guid)dr[0], rootname, namefield, valuefield, childfield));
            }
            sb.Append("]");
            sb.Replace("},]", "}]");
            return sb.ToString();
        }

        /// <summary>
        /// 获取分类树形数据(XML格式)
        /// <para>如：GetXmlTreeText("select [id],[title] from [tmenu] where [parentid]='{0}' order by id", pid);</para>
        /// </summary>
        /// <param name="sqlTemplate"></param>
        /// <param name="parentID"></param>
        /// <param name="nodename"></param>
        /// <param name="idname"></param>
        /// <param name="namename"></param>
        /// <returns></returns>
        public string GetXmlTreeText(string sqlTemplate, Guid parentID, string nodename = "node", string idname = "id", string namename = "name")
        {
            if (sqlTemplate.IndexOf("{0}") == -1) return "<{2} {3}=\"{0}\" {4}=\"{1}\" />".Fmt(Guid.Empty, "禁止死循环查询！", nodename, idname, namename);
            StringBuilder sb = new StringBuilder();
            foreach (DataRow dr in this.dbh.ExecuteDataTable(sqlTemplate.Fmt(parentID)).Rows)
            {
                sb.AppendFormat("<{2} {3}=\"{0}\" {4}=\"{1}\">", dr[0], dr[1], nodename, idname, namename);
                sb.Append(GetXmlTreeText(sqlTemplate, dr[0].ToString().ToGuid(), nodename, idname, namename));
                sb.AppendFormat("</{0}>", nodename);
            }
            return sb.ToString();
        }

        /// <summary>
        /// 获取所有父级编号列表(包括自己)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public IList<Guid> GetParentIds<T>(Guid id)
        {
            if (id == Guid.Empty) return new List<Guid>();
            IList<Guid> list = new List<Guid>() { id };
            string sql = "select ParentID from [{0}] where ID='{{0}}'".Fmt(typeof(T).Name);
            this.GetParentIdsFnc(ref list, sql, id);
            return list;
        }
        private void GetParentIdsFnc(ref IList<Guid> list, string tplSql, Guid id)
        {
            foreach (DataRow r in this.dbh.ExecuteDataTable(tplSql.Fmt(id)).Rows)
            {
                list.Add(r[0].ToString().ToGuid());
                this.GetParentIdsFnc(ref list, tplSql, r[0].ToString().ToGuid());
            }
        }

        /// <summary>
        /// 获取所有子级编号列表(不包括自己)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parentID"></param>
        /// <param name="parentname"></param>
        /// <param name="idname"></param>
        /// <returns></returns>
        public IList<Guid> GetChildIDs<T>(Guid parentID, string parentname = "ParentID", string idname = "ID")
        {
            IList<Guid> list = new List<Guid>();
            string sql = "select {1} from [{0}] where {2}='{{0}}'".Fmt(typeof(T).Name, idname, parentname);
            this.GetChildIDsFnc(ref list, sql, parentID);
            return list;
        }
        private void GetChildIDsFnc(ref IList<Guid> list, string sqlTemplate, Guid parentID)
        {
            foreach (DataRow dr in this.dbh.ExecuteDataTable(sqlTemplate.Fmt(parentID)).Rows)
            {
                list.Add(dr[0].ToString().ToGuid());
                GetChildIDsFnc(ref list, sqlTemplate, (Guid)dr[0]);
            }
        }

        /// <summary>
        /// 获取分类树形数据(DataTable表)
        /// </summary>
        /// <param name="sqlTemplate">如：select ID,Name from {0} where ParentID='{{0}}'</param>
        /// <param name="parentID"></param>
        /// <returns></returns>
        public DataTable GetTreeTable(string sqlTemplate, Guid parentID)
        {
            DataTable dt = new DataTable("TreeTable");
            if (sqlTemplate.IndexOf("{0}") == -1) return dt;
            this.GetTreeTableFnc(ref dt, sqlTemplate, parentID);
            dt.TableName = "TreeTable";
            return dt;
        }

        /// <summary>
        /// GetTreeTable()子方法
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="sqlTemplate"></param>
        /// <param name="parentID"></param>
        /// <param name="loop"></param>
        private void GetTreeTableFnc(ref DataTable dataTable, string sqlTemplate, Guid parentID, int loop = 0)
        {
            if (dataTable.Columns.Count == 0)
            {
                dataTable = this.dbh.ExecuteDataTable(dbh.SQL_SetLimit(sqlTemplate, 0).Fmt(Guid.Empty));
            }
            DataRow r;
            foreach (DataRow dr in this.dbh.ExecuteDataTable(sqlTemplate.Fmt(parentID)).Rows)
            {
                r = dataTable.NewRow();
                r.ItemArray = dr.ItemArray;
                r["Name"] = "{0}{1}".Fmt("--".Repeat(loop), r["Name"]);
                dataTable.Rows.Add(r);
                GetTreeTableFnc(ref dataTable, sqlTemplate, (Guid)dr[0], loop + 1);
            }
        }
        #endregion

        #region 直接数据库操作

        /// <summary>
        /// 获取指定查询的数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            DataTable dt = dbh.ExecuteDataTable(sql);
            this.CheckDbhMessage();
            return dt;
        }

        public int ExecuteSql(string sql)
        {
            return dbh.ExecuteNonQuery(sql);
        }
        #endregion
    }
}
